package com.genius.unite_platform.hrm.service.impl;

import java.io.OutputStream;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.adminmanager.other.entity.FillAddJson;
import com.genius.adminmanager.other.entity.ResponseJson;
import com.genius.adminmanager.other.entity.SessionJson;
import com.genius.adminmanager.user.entity.UserJson;
import com.genius.dingding.util.FileUtils;
import com.genius.unite_platform.dict_info.entity.Dict_infoEntity;
import com.genius.unite_platform.hrm.service.Hrm_jdServiceAdapter;
import com.genius.workflow.config.DefaultConfiguration;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.page.PageResult;
import com.genius.workflow.page.SearchResult;
import com.genius.workflow.page.SqlLang;
import com.genius.workflow.util.ConnectionException;
import com.genius.workflow.util.DBClosedException;
import com.genius.workflow.util.DBTools;
import com.genius.workflow.util.DBTools2;
import com.genius.workflow.util.DBUtils;
import com.genius.workflow.util.ExportExcel;


@Service("hrm_jdSelectService")
public class Hrm_jdSelectServiceImpl extends Hrm_jdServiceAdapter{
	@Override
	public JSONObject import_dudao(JSONObject json, JSONObject response,HttpSession session) {    	
    	try {
    		DBTools2.deleteBach("dManageDate", "hrm_jd_dudao", json.getJSONArray("Dtl"));
    		JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			String insertField = "dManageDate,shopCode,shopName,branch,type,startDate,endDate,"
					+ "sEmployeeNO,sEmployeeName,sZoneMagNO,sZoneMagName,sBreadManage,sBreadQuality,sBreadEmployeeName,sWestManage,sWestQuality,sWestEmployeeName,lrr,lrsj=now()";
			String insertTable = "hrm_jd_dudao";
			DBTools2.insertBatch(insertField, insertTable,json.getJSONArray("Dtl"));
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}
		return response;
	}
	@Override
	public JSONObject get_dudao(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sEmployeeNameString="";
		String sZoneMagNameString="";
		if( json.getString("sZoneMagName") != null && !json.getString("sZoneMagName").equals("") ){
			sZoneMagNameString = " and sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if( json.getString("sEmployeeName") != null && !json.getString("sEmployeeName").equals("") ){
			sEmployeeNameString = " and sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		if( json.getString("shopname") != null && !json.getString("shopname").equals("") ){
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if( json.getString("shopcode") != null && !json.getString("shopcode").equals("") ){
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
        String selectSB = "select * from hrm_jd_dudao where 1=1 and dManageDate  = '"+json.getString("whereDate")+"' "+shopcodeString+shopnameString+sEmployeeNameString+sZoneMagNameString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject import_fendian(JSONObject json, JSONObject response,HttpSession session) {    	
    	try {
    		DBTools2.deleteBach("targetDate", "hrm_jd_fendian", json.getJSONArray("Dtl"));
    		JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			String insertField = "targetDate,shopCode,shopName,minimum_sale,reasonable_sale,challenge_sale,minimum_gpm,reasonable_gpm,challenge_gpm,lrr,lrsj=now()";
			String insertTable = "hrm_jd_fendian";
			DBTools2.insertBatch(insertField, insertTable,json.getJSONArray("Dtl"));
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}
		return response;
	}
	@Override
	public JSONObject get_fendian(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		if( json.getString("shopcode") != null && !json.getString("shopcode").equals("") ){
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
        String selectSB = "select * from hrm_jd_fendian where 1=1 and targetDate  = '"+json.getString("whereDate")+"' "+shopcodeString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject import_zongjian(JSONObject json, JSONObject response,HttpSession session) {    	
    	try {
    		DBTools2.deleteBach("targetDate", "hrm_jd_zongjian", json.getJSONArray("Dtl"));
			String insertField = "targetDate,sDirectorNO,sDirectorName,minimum_sale,reasonable_sale,challenge_sale,minimum_gpm,reasonable_gpm,challenge_gpm,"
					+ "minimum_gpm_half,reasonable_gpm_half,challenge_gpm_half,minimum_gpm_whole,reasonable_gpm_whole,challenge_gpm_whole,"
					+ "lrr,lrsj=now()";
			String insertTable = "hrm_jd_zongjian";
			DBTools2.insertBatch(insertField, insertTable,json.getJSONArray("Dtl"));
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}
		return response;
	}
	@Override
	public JSONObject get_zongjian(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String sDirectorNameString="";
		if( json.getString("sDirectorName") != null && !json.getString("sDirectorName").equals("") ){
			sDirectorNameString = " and sDirectorName like '"+json.getString("sDirectorName")+"%'";
		}
        String selectSB = "select * from hrm_jd_zongjian where 1=1 and targetDate  = '"+json.getString("whereDate")+"' "+sDirectorNameString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject fillAdd(JSONObject json, JSONObject response) {   
	try {
    		JSONObject addJson = new JSONObject();
    		String sql = "select CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),IFNULL(LPAD(RIGHT(max(NO),4)+1,4,0),'0001'))NO from hrm_jd_performance where NO like concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),'%')";
			HashMap<String, String> sqlMap = new HashMap<>();
			//sqlMap.put("val", "select distinct sOperCenterNO as  value1,sOperCenter as key1 from hrm_jd_dudao order by value1  ");
			JSONObject fillJson = new JSONObject();
			addJson = DBUtils.select(sql);
			JSONObject fillEditJson = DBTools.getFilling(sqlMap);
			fillJson.put(FillAddJson.field, addJson);
			fillJson.put(FillAddJson.select, fillEditJson);
			
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
			response.put(ResponseJson.data,fillJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Fill_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject getSelectBody(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB ="SELECT TABa.shopCode,TABa.shopName,TABa.branch,TABa.type,TABa.sEmployeeNO,TABa.sEmployeeName,TABa.sZoneMagNO,TABa.sZoneMagName, " + 
        		"TABb.days,TABb.totalsalemoney,TABb.totalinmoney,TABb.GPM,TABc.minimum_sale,TABc.reasonable_sale,TABc.challenge_sale,AVG, " + 
        		"ROUND( IFNULL( AVG / minimum_sale, 1 ), 2 ) * 100 AS minimum_percent,ROUND( IFNULL( AVG / reasonable_sale, 0 ), 2 ) * 100 AS reasonable_percent, " + 
        		"ROUND( IFNULL( AVG / challenge_sale, 0 ), 2 ) * 100 AS challenge_percent,	ROUND( IFNULL( minimum_gpm, 0 ), 2 ) minimum_gpm, " + 
        		"ROUND( IFNULL( reasonable_gpm, 0 ), 2 ) reasonable_gpm,ROUND( IFNULL( challenge_gpm, 0 ), 2 ) challenge_gpm,ROUND( IFNULL( GPM_percent, 0 ), 2 ) GPM_percent, " + 
        		"ROUND( IFNULL( GPM_percent / minimum_gpm, 1 ), 2 ) * 100 AS minimum_gpm_percent,ROUND( IFNULL( GPM_percent / reasonable_gpm, 1 ), 2 ) * 100 AS reasonable_gpm_percent, " + 
        		"ROUND( IFNULL( GPM_percent / challenge_gpm, 1 ), 2 ) * 100 AS challenge_gpm_percent  " + 
        		"from  " + 
        		"( " + 
        		"	(SELECT dManageDate,shopCode,shopName,branch,type,startDate,endDate,sEmployeeNO,sEmployeeName,sZoneMagNO,sZoneMagName from hrm_jd_dudao where dManageDate = '"+json.getString("date")+"')TABa " + 
        		"	LEFT JOIN " + 
        		"	(SELECT	b.shopCode,b.endDate,PERIOD_DIFF(b.endDate,b.startDate)+1 days,SUM(SaleAmount)totalsalemoney,SUM(CostAmount)totalinmoney,SUM(Gross)GPM,ROUND(AVG(SaleAmount),2) AVG, " + 
        		"		SUM(Gross)/SUM(SaleAmount)*100 AS GPM_percent FROM	 " + 
        		"		hrm_jd_dudao b	 " + 
        		"		LEFT JOIN  " + 
        		"		(SELECT * FROM jhcvs.jd_maoli WHERE recorddate >= '"+json.getString("date")+"-01' AND recorddate <= '"+json.getString("date")+"-31' ) a ON b.shopCode = a.StoreNO  " + 
        		"		WHERE	dManageDate = '"+json.getString("date")+"' 	AND recorddate >= b.startDate AND recorddate <= b.endDate 	GROUP BY shopcode,endDate  " + 
        		"	)TABb ON TABa.shopcode = TABb.shopcode AND TABa.endDate = TABb.endDate " + 
        		"	LEFT JOIN  " + 
        		"	( SELECT shopcode, shopName, minimum_sale, reasonable_sale, challenge_sale, minimum_gpm, reasonable_gpm, challenge_gpm FROM hrm_jd_fendian WHERE targetDate = '"+json.getString("date")+"')TABc ON TABa.shopcode = TABc.shopcode " + 
        		") ";
        if(json.getString("type").equals("sZoneMagNO")) {
        	selectSB = "SELECT TABa.sZoneMagNO,TABa.sZoneMagName,SUM(totalsalemoney)totalsalemoney,SUM(totalinmoney)totalinmoney,SUM(GPM)GPM,minimum_sale, reasonable_sale, challenge_sale,SUM(totalsalemoney)salesum, " + 
        			"ROUND(IFNULL(SUM(totalsalemoney)/minimum_sale,1)*100,2) AS minimum_percent,ROUND(IFNULL(SUM(totalsalemoney)/reasonable_sale,1)*100,2) AS reasonable_percent, " + 
        			"ROUND(IFNULL(SUM(totalsalemoney)/challenge_sale,1)*100,2) AS challenge_percent,minimum_gpm, reasonable_gpm, challenge_gpm,ROUND(SUM(GPM_usual)/SUM(totalsalemoney_usual)*100,2)GPM_percent, " + 
        			"ROUND(minimum_gpm*SUM(totalsalemoney_usual)/SUM(GPM_usual),2) AS minimum_gpm_percent,ROUND(reasonable_gpm*SUM(totalsalemoney_usual)/SUM(GPM_usual),2) AS reasonable_gpm_percent, " + 
        			"ROUND(challenge_gpm*SUM(totalsalemoney_usual)/SUM(GPM_usual),2) AS challenge_gpm_percent,minimum_gpm_half,reasonable_gpm_half,challenge_gpm_half,ROUND(IFNULL(SUM(GPM_half)/SUM(totalsalemoney_half),0)*100,2)GPM_half, " + 
        			"ROUND(IFNULL(minimum_gpm_half*SUM(totalsalemoney_half)/SUM(GPM_half),1)*100,2) AS minimum_half,ROUND(IFNULL(reasonable_gpm_half*SUM(totalsalemoney_half)/SUM(GPM_half),1)*100,2) AS reasonable_half, " + 
        			"ROUND(IFNULL(challenge_gpm_half*SUM(totalsalemoney_half)/SUM(GPM_half),1)*100,2) AS challenge_half,minimum_gpm_whole,reasonable_gpm_whole,challenge_gpm_whole,ROUND(IFNULL(SUM(GPM_whole)/SUM(totalsalemoney_whole),0)*100,2)GPM_whole, " + 
        			"ROUND(IFNULL(minimum_gpm_whole*SUM(totalsalemoney_whole)/SUM(GPM_whole),1)*100,2) AS minimum_whole,ROUND(IFNULL(reasonable_gpm_whole*SUM(totalsalemoney_whole)/SUM(GPM_whole),1)*100,2) AS reasonable_whole, " + 
        			"ROUND(IFNULL(challenge_gpm_whole*SUM(totalsalemoney_whole)/SUM(GPM_whole),1)*100,2) AS challenge_whole " + 
        			"from  " + 
        			"( " + 
        			"  (SELECT dManageDate,shopCode,shopName,branch,type,startDate,endDate,sEmployeeNO,sEmployeeName,sZoneMagNO,sZoneMagName from hrm_jd_dudao where dManageDate = '2022-01')TABa " + 
        			"  LEFT JOIN " + 
        			"  ( " + 
        			"  SELECT  b.shopCode,b.endDate,PERIOD_DIFF(b.endDate,b.startDate)+1 days,SUM(SaleAmount)totalsalemoney,SUM(CostAmount)totalinmoney,SUM(Gross)GPM,ROUND(AVG(SaleAmount),2) AVG, " + 
        			"    SUM(Gross)/SUM(SaleAmount)*100 AS GPM_percent,IF(type=0,SaleAmount,0)totalsalemoney_usual,IF(type=1,SaleAmount,0)totalsalemoney_half, " + 
        			"    IF(type=2,SaleAmount,0)totalsalemoney_whole,IF(type=0,Gross,0)GPM_usual,IF(type=1,Gross,0)GPM_half, " + 
        			"    IF(type=2,Gross,0)GPM_whole " + 
        			"    FROM   " + 
        			"    hrm_jd_dudao b   " + 
        			"    LEFT JOIN  " + 
        			"    (SELECT * FROM jhcvs.jd_maoli WHERE recorddate >= '2022-01-01' AND recorddate <= '2022-01-31' ) a ON b.shopCode = a.StoreNO  " + 
        			"    WHERE  dManageDate = '2022-01'   AND recorddate >= b.startDate AND recorddate <= b.endDate   GROUP BY shopcode,endDate  " + 
        			"  )TABb ON TABa.shopcode = TABb.shopcode AND TABa.endDate = TABb.endDate " + 
        			"  LEFT JOIN  " + 
        			"  (SELECT sZoneMagNO, sZoneMagName, minimum_sale, reasonable_sale, challenge_sale, minimum_gpm, reasonable_gpm, challenge_gpm, " + 
        			"  minimum_gpm_half,reasonable_gpm_half,challenge_gpm_half,minimum_gpm_whole,reasonable_gpm_whole,challenge_gpm_whole " + 
        			"  FROM hrm_jd_zongjian WHERE targetDate = '2022-01')TABc  " + 
        			"  ON TABa.sZoneMagNO = TABc.sZoneMagNO " + 
        			")group by sZoneMagNO";
        }else if(json.getString("type").equals("sEmployeeNO")) {
        	selectSB = "SELECT " + 
        			"	TABa.sEmployeeNO, " + 
        			"	TABa.sEmployeeName, " + 
        			"	SUM( totalsalemoney ) totalsalemoney, " + 
        			"	SUM( totalinmoney ) totalinmoney, " + 
        			"	SUM( GPM ) GPM, " + 
        			"	ROUND(SUM(minimum_sale*days),2)minimum_sale, " + 
        			"	ROUND(SUM(reasonable_sale*days),2)reasonable_sale, " + 
        			"	ROUND(SUM(challenge_sale*days),2)challenge_sale, " + 
        			"	SUM( totalsalemoney ) salesum, " + 
        			"	ROUND( IFNULL( SUM( totalsalemoney ) / SUM(minimum_sale*days), 1 ) * 100, 2 ) AS minimum_percent, " + 
        			"	ROUND( IFNULL( SUM( totalsalemoney ) / SUM(reasonable_sale*days), 1 ) * 100, 2 ) AS reasonable_percent, " + 
        			"	ROUND( IFNULL( SUM( totalsalemoney ) / SUM(challenge_sale*days), 1 ) * 100, 2 ) AS challenge_percent, " + 
        			"	ROUND(SUM(TABc.minimum_GPM_usual*days)/SUM(TABc.minimum_sale*days)*100,2) AS minimum_gpm, " + 
        			"	ROUND(SUM(TABc.reasonable_GPM_usual*days)/SUM(TABc.reasonable_sale*days)*100,2) AS reasonable_gpm, " + 
        			"	ROUND(SUM(TABc.challenge_GPM_usual*days)/SUM(TABc.challenge_sale*days)*100,2) AS challenge_gpm, " + 
        			"	ROUND(SUM(GPM_usual)/SUM(totalsalemoney_usual)*100,2) AS GPM_percent, " + 
        			"	ROUND((SUM(GPM_usual)/SUM( totalsalemoney_usual))/(SUM(TABc.minimum_GPM_usual*days)/SUM(TABc.minimum_sale*days))*100,2) AS minimum_gpm_percent, " + 
        			"	ROUND((SUM(GPM_usual)/SUM( totalsalemoney_usual))/(SUM(TABc.reasonable_GPM_usual*days)/SUM(TABc.reasonable_sale*days))*100,2) AS reasonable_gpm_percent, " + 
        			"	ROUND((SUM(GPM_usual)/SUM( totalsalemoney_usual))/(SUM(TABc.challenge_GPM_usual*days)/SUM(TABc.challenge_sale*days))*100,2) AS challenge_gpm_percent, " + 
        			"	ROUND(SUM(TABc.minimum_GPM_half*days)/SUM(TABc.minimum_sale*days)*100,2) AS minimum_gpm_half, " + 
        			"	ROUND(SUM(TABc.reasonable_GPM_half*days)/SUM(TABc.reasonable_sale*days)*100,2) AS reasonable_gpm_half, " + 
        			"	ROUND(SUM(TABc.challenge_GPM_half*days)/SUM(TABc.challenge_sale*days)*100,2) AS challenge_gpm_half, " + 
        			"	IFNULL(ROUND(SUM(GPM_half)/SUM(totalsalemoney_half)*100,2),0) AS GPM_half, " + 
        			"	IFNULL(ROUND((SUM(GPM_half)/SUM( totalsalemoney_half))/(SUM(TABc.minimum_GPM_half*days)/SUM(TABc.minimum_sale*days))*100,2),0) AS minimum_half, " + 
        			"	IFNULL(ROUND((SUM(GPM_half)/SUM( totalsalemoney_half))/(SUM(TABc.reasonable_GPM_half*days)/SUM(TABc.reasonable_sale*days))*100,2),0) AS reasonable_half, " + 
        			"	IFNULL(ROUND((SUM(GPM_half)/SUM( totalsalemoney_half))/(SUM(TABc.challenge_GPM_half*days)/SUM(TABc.challenge_sale*days))*100,2),0) AS challenge_half, " + 
        			"	ROUND(SUM(TABc.minimum_GPM_whole*days)/SUM(TABc.minimum_sale*days)*100,2) AS minimum_gpm_whole, " + 
        			"	ROUND(SUM(TABc.reasonable_GPM_whole*days)/SUM(TABc.reasonable_sale*days)*100,2) AS reasonable_gpm_whole, " + 
        			"	ROUND(SUM(TABc.challenge_GPM_whole*days)/SUM(TABc.challenge_sale*days)*100,2) AS challenge_gpm_whole, " + 
        			"	IFNULL(ROUND(SUM(GPM_whole)/SUM(totalsalemoney_whole)*100,2),0) AS GPM_whole, " + 
        			"	IFNULL(ROUND((SUM(GPM_whole)/SUM( totalsalemoney_whole))/(SUM(TABc.minimum_GPM_whole*days)/SUM(TABc.minimum_sale*days))*100,2),0) AS minimum_whole, " + 
        			"	IFNULL(ROUND((SUM(GPM_whole)/SUM( totalsalemoney_whole))/(SUM(TABc.reasonable_GPM_whole*days)/SUM(TABc.reasonable_sale*days))*100,2),0) AS reasonable_whole, " + 
        			"	IFNULL(ROUND((SUM(GPM_whole)/SUM( totalsalemoney_whole))/(SUM(TABc.challenge_GPM_whole*days)/SUM(TABc.challenge_sale*days))*100,2),0) AS challenge_whole " + 
        			"FROM " + 
        			"	( " + 
        			"		( " + 
        			"		SELECT dManageDate,shopCode,shopName,branch,type,startDate,endDate,sEmployeeNO,sEmployeeName,sZoneMagNO,sZoneMagName  " + 
        			"		FROM hrm_jd_dudao WHERE dManageDate = '2022-01'  " + 
        			"		) TABa " + 
        			"		LEFT JOIN ( " + 
        			"		SELECT " + 
        			"			b.shopCode, " + 
        			"			b.endDate, " + 
        			"			PERIOD_DIFF( b.endDate, b.startDate ) + 1 days, " + 
        			"			SUM( SaleAmount ) totalsalemoney, " + 
        			"			SUM( CostAmount ) totalinmoney, " + 
        			"			SUM( Gross ) GPM, " + 
        			"			ROUND( AVG( SaleAmount ), 2 ) AVG, " + 
        			"			SUM( Gross ) / SUM( SaleAmount ) * 100 AS GPM_percent, " + 
        			"			IF( type = 0, SaleAmount, 0 ) totalsalemoney_usual, " + 
        			"			IF( type = 1, SaleAmount, 0 ) totalsalemoney_half, " + 
        			"		  IF( type = 2, SaleAmount, 0 ) totalsalemoney_whole, " + 
        			"		  IF( type = 0, Gross, 0 ) GPM_usual, " + 
        			"		  IF( type = 1, Gross, 0 ) GPM_half, " + 
        			"		  IF( type = 2, Gross, 0 ) GPM_whole  " + 
        			"		FROM " + 
        			"			hrm_jd_dudao b " + 
        			"			LEFT JOIN ( SELECT * FROM jhcvs.jd_maoli WHERE recorddate >= '2022-01-01' AND recorddate <= '2022-01-31' ) a ON b.shopCode = a.StoreNO  " + 
        			"		WHERE " + 
        			"			dManageDate = '2022-01'  " + 
        			"			AND recorddate >= b.startDate  " + 
        			"			AND recorddate <= b.endDate  " + 
        			"		GROUP BY " + 
        			"			shopcode, " + 
        			"			endDate  " + 
        			"		) TABb ON TABa.shopcode = TABb.shopcode  " + 
        			"		AND TABa.endDate = TABb.endDate " + 
        			"		LEFT JOIN  " + 
        			"		( " + 
        			"		SELECT a.shopcode, a.shopName, minimum_sale, reasonable_sale, challenge_sale, minimum_gpm, reasonable_gpm, challenge_gpm, " + 
        			"		IF( type = 0, minimum_sale*minimum_gpm*0.01, 0 ) minimum_GPM_usual, " + 
        			"		IF( type = 0, reasonable_sale*reasonable_gpm*0.01, 0 ) reasonable_GPM_usual, " + 
        			"		IF( type = 0, challenge_sale*challenge_gpm*0.01, 0 ) challenge_GPM_usual, " + 
        			"		 " + 
        			"		IF( type = 1, minimum_sale*minimum_gpm*0.01, 0 ) minimum_GPM_half, " + 
        			"		IF( type = 1, reasonable_sale*reasonable_gpm*0.01, 0 ) reasonable_GPM_half, " + 
        			"		IF( type = 1, challenge_sale*challenge_gpm*0.01, 0 ) challenge_GPM_half, " + 
        			"		 " + 
        			"		IF( type = 2, minimum_sale*minimum_gpm*0.01, 0 ) minimum_GPM_whole, " + 
        			"		IF( type = 2, reasonable_sale*reasonable_gpm*0.01, 0 ) reasonable_GPM_whole, " + 
        			"		IF( type = 2, challenge_sale*challenge_gpm*0.01, 0 ) challenge_GPM_whole " + 
        			" " + 
        			"		FROM hrm_jd_fendian a LEFT JOIN hrm_jd_dudao b on a.shopCode = b.shopCode and a.targetDate = b.dManageDate WHERE targetDate = '2022-01' " + 
        			"		) TABc ON TABa.shopcode = TABc.shopcode  " + 
        			"	)  " + 
        			"GROUP BY " + 
        			"	sEmployeeNO";
        }
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject getperformance(JSONObject json, JSONObject response) {
		JSONObject searchJson = json.getJSONObject("search");
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String nameString="";
		String typeString="";
		if(searchJson.getString("name")!= null && !searchJson.getString("name").equals("")){
			nameString= " and name like '%"+searchJson.getString("name")+"%'";
		}
		if(searchJson.getString("type")!= null && !searchJson.getString("type").equals("")){
			typeString= " and type = '"+searchJson.getString("type")+"'";
		}
        String selectSB = "select NO,name,date,type,val,flag,(select name from t_user where userId = a.lrr)lrr,lrsj,"
        		+ "(select name from t_user where userId = a.shr)shr,shsj,(select name from t_user where userId = a.zfr)zfr,zfsj from hrm_jd_performance a "
        		+ "where date = '"+searchJson.getString("date")+"'"+nameString+typeString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject add(JSONObject json, JSONObject response,HttpSession session) {  
    	// 先查询主键是否存在
    	try {
	    	String keywords = "NO";
	    	String insertTable = "hrm_jd_performance";
	    	String insertField = "NO,name,date,type,val,flag,lrr,lrsj=now()";
			JSONObject existJson = DBTools2.getIsExist(keywords,insertTable, json);
			if(existJson != null){
				response.put(ResponseJson.msg, "已存在该数据");
				response.put(ResponseJson.status, ResponseJson.status_Failed);
				return response;
			}
			JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			JSONObject infoJson = json.getJSONObject("performance");
			infoJson.put(Dict_infoEntity.lrr, userJson.getString(UserJson.userId));
			LinkedList<SQLBean> beanList = new LinkedList<SQLBean>();
			String infoSql = DBTools.getInsertSql(insertField, insertTable);
			String[] infoParam = DBTools.getInsertParms(insertField, infoJson);
			SQLBean infoBean = new SQLBean(infoSql, infoParam);
			beanList.add(infoBean);
			//结果录入
			JSONArray insertArray = json.getJSONArray("detail");
			if(insertArray.size() > 0){
				String insertArrayField = "NO='"+infoJson.getString("NO")+"',shopCode,shopName,branch,type,sZoneMagNO,sZoneMagName,sEmployeeNO,sEmployeeName,"
						+ "days,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,AVG,minimum_percent,reasonable_percent,challenge_percent,"
						+ "minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent,salesum,GPM_half,"
						+ "GPM_whole,minimum_gpm_half,reasonable_gpm_half,challenge_gpm_half,minimum_half,reasonable_half,challenge_half,minimum_gpm_whole,"
						+ "reasonable_gpm_whole,challenge_gpm_whole,minimum_whole,reasonable_whole,challenge_whole";
				String arrayInsertSql = DBTools.getInsertSql(insertArrayField, "hrm_jd_performance_detail");
				List<String[]> arrayInsertParams = DBTools.getInsertListParms(insertArrayField, insertArray);	
				SQLBean insertArrayBean = new SQLBean(arrayInsertSql, arrayInsertParams);
				beanList.add(insertArrayBean);
			}
			DBTools.executeBatch(beanList);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		}
		return response;
		
	}
	@Override
	public JSONObject getsearch(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = " select *  from hrm_jd_performance_detail where NO = '"+json.getString("NO")+"'  "+ "order by  branch";
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject fillEdit(JSONObject json, JSONObject response) {   
	try {
    		JSONObject addJson = new JSONObject();
    		String selectField = "NO,name,date,type,val";
    		String whereField = "NO";
    		String table = "hrm_jd_performance";
			HashMap<String, String> sqlMap = new HashMap<>();
			JSONObject fillJson = new JSONObject();
			addJson = DBTools.getFilling(selectField,whereField,table,json,addJson);
			JSONObject fillEditJson = DBTools.getFilling(sqlMap);
			fillJson.put(FillAddJson.field, addJson);
			fillJson.put(FillAddJson.select, fillEditJson);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
			response.put(ResponseJson.data,fillJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Edit_Fill_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject feiyong_getSelectBody(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		String sEmployeeName="";
		String sZoneMagName="";
		if(json.getString("shopcode") != null && !json.getString("shopcode").equals("")){
			shopcodeString = " and shopcode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("sEmployeeName") != null && !json.getString("sEmployeeName").equals("")){
			sEmployeeName = " and sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		if(json.getString("sZoneMagName") != null && !json.getString("sZoneMagName").equals("")){
			sZoneMagName = " and sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
        String selectSB =" SELECT *,pandian AS pdAmount,ROUND(pandian/SaleAmount*100,2)pd_percent,(EatAmount+AdjAmount+pandian)sumAmount,ROUND((EatAmount+AdjAmount+pandian)/SaleAmount*100,2) sum_percent  from( " + 
        		"(SELECT * from hrm_jd_dudao  where dManageDate = '"+json.getString("date")+"')TABa " + 
        		"LEFT JOIN  " + 
        		"(SELECT StoreNO,SUM(SaleAmount)SaleAmount,SUM(EatAmount)EatAmount,ROUND(SUM(EatAmount)/SUM(SaleAmount)*100,2)Eat_percent,SUM(AdjAmount)AdjAmount,ROUND(SUM(AdjAmount)/SUM(SaleAmount)*100,2)Adj_percent " + 
        		" FROM jhcvs.jd_maoli where recorddate <= '"+json.getString("date")+"-01' and recorddate <= '"+json.getString("date")+"-31' " + 
        		"GROUP BY StoreNO)TABb on TABa.shopCode = TABb.StoreNO " + 
        		"LEFT JOIN(" + 
        		"	SELECT sStoreNO,sum(nCheckAmount-nEndAmount) pandian FROM jhcvs.jd_tinventorydiffer WHERE dInventoryDate >= '"+json.getString("date")+"-01 00:00:00' " + 
        		"	and dInventoryDate <= '"+json.getString("date")+"-31 23:59:59' group by sStoreNO " + 
        		" )TABc ON TABa.shopCode = TABc.sStoreNO "+
        		")where 1=1 "+shopcodeString+sEmployeeName+sZoneMagName;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
      try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject feiyong_add(JSONObject json, JSONObject response,HttpSession session) {  
    	// 先查询主键是否存在
    	try {
	    	String keywords = "NO";
	    	String insertTable = "hrm_jd_feiyong";
	    	String insertField = "NO,name,date,shopcode,sEmployeeName,sZoneMagName,flag,lrr,lrsj=now()";
			JSONObject existJson = DBTools2.getIsExist(keywords,insertTable, json);
			if(existJson != null){
				response.put(ResponseJson.msg, "已存在该数据");
				response.put(ResponseJson.status, ResponseJson.status_Failed);
				return response;
			}
			JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			JSONObject infoJson = json.getJSONObject("performance");
			infoJson.put(Dict_infoEntity.lrr, userJson.getString(UserJson.userId));
			LinkedList<SQLBean> beanList = new LinkedList<SQLBean>();
			String infoSql = DBTools.getInsertSql(insertField, insertTable);
			String[] infoParam = DBTools.getInsertParms(insertField, infoJson);
			SQLBean infoBean = new SQLBean(infoSql, infoParam);
			beanList.add(infoBean);
			//结果录入
			JSONArray insertArray = json.getJSONArray("detail");
			if(insertArray.size() > 0){
				String insertArrayField = "NO='"+infoJson.getString("NO")+"',shopCode,shopName,branch,type,sZoneMagNO,sZoneMagName,sEmployeeNO,sEmployeeName,"
						+"sBreadManage,sBreadQuality,sBreadEmployeeName,sWestManage,sWestQuality,sWestEmployeeName,SaleAmount,EatAmount,Eat_percent,AdjAmount,"
						+ "Adj_percent,pdAmount,pd_percent,sumAmount,sum_percent";
				String arrayInsertSql = DBTools.getInsertSql(insertArrayField, "hrm_jd_feiyong_detail");
				List<String[]> arrayInsertParams = DBTools.getInsertListParms(insertArrayField, insertArray);	
				SQLBean insertArrayBean = new SQLBean(arrayInsertSql, arrayInsertParams);
				beanList.add(insertArrayBean);
			}
			DBTools.executeBatch(beanList);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		}
		return response;
		
	}
	@Override
	public JSONObject feiyong_getperformance(JSONObject json, JSONObject response) {
		JSONObject searchJson = json.getJSONObject("search");
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String nameString="";
		String typeString="";
		if(searchJson.getString("name")!= null && !searchJson.getString("name").equals("")){
			nameString= " and name like '%"+searchJson.getString("name")+"%'";
		}
		if(searchJson.getString("flag")!= null && !searchJson.getString("flag").equals("")){
			typeString= " and flag = "+searchJson.getString("flag")+"";
		}
        String selectSB = "select NO,name,date,flag,(select name from t_user where userId = a.lrr)lrr,lrsj,"
        		+ "(select name from t_user where userId = a.shr)shr,shsj,(select name from t_user where userId = a.zfr)zfr,zfsj from hrm_jd_feiyong a "
        		+ "where date = '"+searchJson.getString("date")+"'"+nameString+typeString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject feiyong_fillEdit(JSONObject json, JSONObject response) {   
	try {
    		JSONObject addJson = new JSONObject();
    		String selectField = "NO,name,date,shopcode,sEmployeeName,sZoneMagName";
    		String whereField = "NO";
    		String table = "hrm_jd_feiyong";  
			HashMap<String, String> sqlMap = new HashMap<>();
			JSONObject fillJson = new JSONObject();
			addJson = DBTools.getFilling(selectField,whereField,table,json,addJson);
			JSONObject fillEditJson = DBTools.getFilling(sqlMap);
			fillJson.put(FillAddJson.field, addJson);
			fillJson.put(FillAddJson.select, fillEditJson);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
			response.put(ResponseJson.data,fillJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Edit_Fill_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject feiyong_getBody(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and a.shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and a.sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and a.sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB = " select a.* from hrm_jd_feiyong_detail a right join hrm_jd_feiyong b on a.NO = b.NO "
				+ "where b.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sZoneMagNameString+sEmployeeNameString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
        System.err.println(">>"+response+"-"+response.size());
		return response;
	}
	@Override
	public JSONObject feiyong_fillAdd(JSONObject json, JSONObject response) {   
	try {
    		JSONObject addJson = new JSONObject();
    		String sql = "select CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),IFNULL(LPAD(RIGHT(max(NO),4)+1,4,0),'0001'))NO from hrm_jd_feiyong where NO like concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),'%')";
			HashMap<String, String> sqlMap = new HashMap<>();
			JSONObject fillJson = new JSONObject();
			addJson = DBUtils.select(sql);
			JSONObject fillEditJson = DBTools.getFilling(sqlMap);
			fillJson.put(FillAddJson.field, addJson);
			fillJson.put(FillAddJson.select, fillEditJson);
			
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
			response.put(ResponseJson.data,fillJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Fill_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject feiyong_batchdel(JSONObject json, JSONObject response) {
		String whereField = "NO";
    	String delTable = "hrm_jd_feiyong";
    	try {
			DBTools2.delete(whereField, delTable,json);
			DBTools2.delete(whereField, "hrm_jd_feiyong_detail",json);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject feiyong_toshenhe(JSONObject json, JSONObject response) {
		JSONArray json1=json.getJSONArray("shenheData");
		int c = json.getJSONArray("shenheData").size();    
		String whereField = "NO";
    	String updateTable = "hrm_jd_feiyong";
    	String updateField = "flag,shr,shsj=now()";
    	try { 
    		DBTools2.updateBatch(updateField, whereField, updateTable, json1);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_finish_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;  
	}
	@Override
	public JSONObject feiyong_tozuofei(JSONObject json, JSONObject response) {
		JSONArray json1=json.getJSONArray("zuofeiData");
		int c = json.getJSONArray("zuofeiData").size();    
		String whereField = "NO";
    	String updateTable = "hrm_jd_feiyong";
    	String updateField = "flag,zfr,zfsj=now()";
    	try { 
    		DBTools2.updateBatch(updateField, whereField, updateTable, json1);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_finish_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;  
	}
	@Override
	public JSONObject feiyong_getsearch(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
        String selectSB = " select *  from hrm_jd_feiyong_detail where NO = '"+json.getString("NO")+"'  "+ "order by  shopcode,branch";
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject feiyong_exportExcel(HttpServletRequest request, HttpServletResponse responseServlet,JSONObject response) {
		JSONObject json = JSONObject.parseObject(request.getParameter("search"));
		SqlLang sql = null;
        String selectSB = " select *  from hrm_jd_feiyong_detail where NO = '"+json.getString("NO")+"'  "+ "order by  shopcode,branch";
        String format =  request.getParameter(PageResult.format);
		String	tableTitle ="店号,店名,分店属性,分店类型,区域总监,区域督导,现烤经理,现烤品质经理,现烤品质督导,西点经理,西点品质经理,西点品质督导,销售额,试吃金额,试吃占比,报损金额,报损占比,盘点金额,盘点占比,合计金额,合计占比";
		String	tableField ="shopCode,shopName,branch,type,sZoneMagName,sEmployeeName,sBreadManage,sBreadQuality,sBreadEmployeeName,sWestManage,sWestQuality,sWestEmployeeName,SaleAmount,EatAmount,Eat_percent,AdjAmount,Adj_percent,pdAmount,pd_percent,sumAmount,sum_percent";

		String [] tableTitles = tableTitle.split(DefaultConfiguration.separator_douhao);
		String [] tableFields = tableField.split(DefaultConfiguration.separator_douhao);
		OutputStream out = null;
		try {
			JSONArray arr = DBTools2.selectArr(selectSB);
			PageResult.formatType type = PageResult.formatType.valueOf(format);
			String fileName = "";
			switch(type){
				case excel:
					fileName = "费用查询.xls";
					break;
				case csv:
					fileName = "费用查询.csv";
					break;
				case pdf:
					break;
				case word:
					break;
			}
			fileName = FileUtils.encodeFilename(fileName, request);
			String headStr = "attachment; filename=\"" + fileName + "\"";
			responseServlet.setHeader("content-Type", "application/vnd.ms-excel");
			responseServlet.setHeader("Content-Disposition", headStr);
			out = responseServlet.getOutputStream();
			ExportExcel ex = new ExportExcel("费用查询", tableTitles,tableFields, arr);
			ex.export(out);
			out.flush();
			out.close();
			response.put(ResponseJson.status, 1002);
			response.put(ResponseJson.msg, "删除成功");
		} catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
		} catch (Exception e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Export_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject mendian_exportExcel(HttpServletRequest request, HttpServletResponse responseServlet,JSONObject response) {
		JSONObject json = JSONObject.parseObject(request.getParameter("search"));
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and a.shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and a.sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and a.sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB = " select a.* from hrm_jd_feiyong_detail a right join hrm_jd_feiyong b on a.NO = b.NO "
				+ "where b.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sZoneMagNameString+sEmployeeNameString;
        String format =  request.getParameter(PageResult.format);
		String	tableTitle ="店号,店名,分店属性,分店类型,区域总监,区域督导,现烤经理,现烤品质经理,现烤品质督导,西点经理,西点品质经理,西点品质督导,销售额,试吃金额,试吃占比,报损金额,报损占比,盘点金额,盘点占比,合计金额,合计占比";
		String	tableField ="shopCode,shopName,branch,type,sZoneMagName,sEmployeeName,sBreadManage,sBreadQuality,sBreadEmployeeName,sWestManage,sWestQuality,sWestEmployeeName,SaleAmount,EatAmount,Eat_percent,AdjAmount,Adj_percent,pdAmount,pd_percent,sumAmount,sum_percent";

		String [] tableTitles = tableTitle.split(DefaultConfiguration.separator_douhao);
		String [] tableFields = tableField.split(DefaultConfiguration.separator_douhao);
		OutputStream out = null;
		try {
			JSONArray arr = DBTools2.selectArr(selectSB);
			PageResult.formatType type = PageResult.formatType.valueOf(format);
			String fileName = "";
			switch(type){
				case excel:
					fileName = "费用查询.xls";
					break;
				case csv:
					fileName = "费用查询.csv";
					break;
				case pdf:
					break;
				case word:
					break;
			}
			fileName = FileUtils.encodeFilename(fileName, request);
			String headStr = "attachment; filename=\"" + fileName + "\"";
			responseServlet.setHeader("content-Type", "application/vnd.ms-excel");
			responseServlet.setHeader("Content-Disposition", headStr);
			out = responseServlet.getOutputStream();
			ExportExcel ex = new ExportExcel("费用查询", tableTitles,tableFields, arr);
			ex.export(out);
			out.flush();
			out.close();
			response.put(ResponseJson.status, 1002);
			response.put(ResponseJson.msg, "删除成功");
		} catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
		} catch (Exception e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Export_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject getBody(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and a.shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and a.sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and a.sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB =" select a.*  from hrm_jd_performance_detail a RIGHT JOIN hrm_jd_performance b on a.NO = b.NO and b.flag=1 where b.type = '"+json.getString("group")+"' and b.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sZoneMagNameString+sEmployeeNameString+
				 "group by "+json.getString("group")+",branch  order by "+(json.getString("group").equals("shopCode")?"shopCode":json.getString("group"))+",branch";
        System.err.println("!"+selectSB);
		String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject dudao_exportExcel(HttpServletRequest request, HttpServletResponse responseServlet,JSONObject response) {
		JSONObject json = JSONObject.parseObject(request.getParameter("search"));
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and a.shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and a.sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and a.sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB =" select a.*  from hrm_jd_performance_detail a RIGHT JOIN hrm_jd_performance b on a.NO = b.NO and b.flag=1 where b.type = '"+json.getString("group")+"' and b.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sZoneMagNameString+sEmployeeNameString+
				 "group by "+json.getString("group")+",branch  order by "+(json.getString("group").equals("shopCode")?"shopCode":json.getString("group"))+",branch";
        String format =  request.getParameter(PageResult.format);
		String tableTitle ="店号,店名,门店性质,门店类型,区域总监,区域督导,销售天数,含税销售额,含税销售成本,销售毛利,日均保底目标,日均合理目标,日均挑战目标,单店实际日均销售,日均保底达成比,日均合理达成比,日均挑战达成比,毛利率保底目标,毛利率合理目标,毛利率挑战目标,毛利率实际值,毛利率保底达成比,毛利率合理达成比,毛利率挑战达成比";
		String tableField ="shopCode,shopName,branch,type,sZoneMagName,sEmployeeName,days,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,AVG,minimum_percent,reasonable_percent,challenge_percent,minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent";
		String [] tableTitles = tableTitle.split(DefaultConfiguration.separator_douhao);
		String [] tableFields = tableField.split(DefaultConfiguration.separator_douhao);
		OutputStream out = null;
		try {
			JSONArray arr = DBTools2.selectArr(selectSB);
			PageResult.formatType type = PageResult.formatType.valueOf(format);
			String fileName = "";
			switch(type){
				case excel:
					fileName = "督导绩效查询.xls";
					break;
				case csv:
					fileName = "督导绩效查询.csv";
					break;
				case pdf:
					break;
				case word:
					break;
			}
			fileName = FileUtils.encodeFilename(fileName, request);
			String headStr = "attachment; filename=\"" + fileName + "\"";
			responseServlet.setHeader("content-Type", "application/vnd.ms-excel");
			responseServlet.setHeader("Content-Disposition", headStr);
			out = responseServlet.getOutputStream();
			ExportExcel ex = new ExportExcel("督导绩效查询", tableTitles,tableFields, arr);
			ex.export(out);
			out.flush();
			out.close();
			response.put(ResponseJson.status, 1002);
			response.put(ResponseJson.msg, "删除成功");
		} catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
		} catch (Exception e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Export_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject feidudao_exportExcel(HttpServletRequest request, HttpServletResponse responseServlet,JSONObject response) {
		JSONObject json = JSONObject.parseObject(request.getParameter("search"));
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sDirectorNameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sDirectorName")!= null && !json.getString("sDirectorName").equals("")) {
			sDirectorNameString = " and sDirectorName like '%"+json.getString("sDirectorName")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB =" select a.*  from hrm_jd_performance_detail a RIGHT JOIN hrm_jd_performance b on a.NO = b.NO and b.flag=1 where b.type = '"+json.getString("group")+"' and b.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sZoneMagNameString+sEmployeeNameString+
				 "group by "+json.getString("group")+",branch  order by "+(json.getString("group").equals("shopCode")?"shopCode":json.getString("group"))+",branch";
        String format =  request.getParameter(PageResult.format);
		String tableTitle ="总监,含税销售金额,含税销售成本,含税销售毛利,月销售保底目标,月销售售合理目标,月销售售挑战目标,实际月销售额,月销售保底达成比,月销售合理达成比,月销售挑战达成比,常规店毛利率保底目标,常规店毛利率合理目标,常规店毛利率挑战目标,常规店毛利率实际值,常规店毛利率保底达成比,常规店毛利率合理达成比,常规店毛利率挑战达成比,半卫星店毛利率保底目标,半卫星店毛利率合理目标,半卫星店毛利率挑战目标,半卫星店毛利率实际值,半卫星店毛利率保底达成比,半卫星店毛利率合理达成比,半卫星店毛利率挑战达成比,纯卫星店毛利率保底目标,纯卫星店毛利率合理目标,纯卫星店毛利率挑战目标,纯卫星店毛利率实际值,纯卫星店毛利率保底达成比,纯卫星店毛利率合理达成比,纯卫星店毛利率挑战达成比";
		String tableField ="sZoneMagName,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,salesum,minimum_percent,reasonable_percent,challenge_percent,minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent,minimum_gpm_half,reasonable_gpm_half,challenge_gpm_half,GPM_half,minimum_half,reasonable_half,challenge_half,minimum_gpm_whole,reasonable_gpm_whole,challenge_gpm_whole,GPM_whole,minimum_whole,reasonable_whole,challenge_whole";
		if(json.getString("group").equals("sEmployeeNO")) {
			tableTitle ="督导,含税销售金额,含税销售成本,含税销售毛利,月销售保底目标,月销售售合理目标,月销售售挑战目标,实际月销售额,月销售保底达成比,月销售合理达成比,月销售挑战达成比,常规店毛利率保底目标,常规店毛利率合理目标,常规店毛利率挑战目标,常规店毛利率实际值,常规店毛利率保底达成比,常规店毛利率合理达成比,常规店毛利率挑战达成比,半卫星店毛利率保底目标,半卫星店毛利率合理目标,半卫星店毛利率挑战目标,半卫星店毛利率实际值,半卫星店毛利率保底达成比,半卫星店毛利率合理达成比,半卫星店毛利率挑战达成比,纯卫星店毛利率保底目标,纯卫星店毛利率合理目标,纯卫星店毛利率挑战目标,纯卫星店毛利率实际值,纯卫星店毛利率保底达成比,纯卫星店毛利率合理达成比,纯卫星店毛利率挑战达成比";
			tableField ="sEmployeeName,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,salesum,minimum_percent,reasonable_percent,challenge_percent,minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent,minimum_gpm_half,reasonable_gpm_half,challenge_gpm_half,GPM_half,minimum_half,reasonable_half,challenge_half,minimum_gpm_whole,reasonable_gpm_whole,challenge_gpm_whole,GPM_whole,minimum_whole,reasonable_whole,challenge_whole";
			
		}
		String [] tableTitles = tableTitle.split(DefaultConfiguration.separator_douhao);
		String [] tableFields = tableField.split(DefaultConfiguration.separator_douhao);
		OutputStream out = null;
		try {
			JSONArray arr = DBTools2.selectArr(selectSB);
			PageResult.formatType type = PageResult.formatType.valueOf(format);
			String fileName = "";
			switch(type){
				case excel:
					fileName = "非督导绩效查询.xls";
					break;
				case csv:
					fileName = "非督导绩效查询.csv";
					break;
				case pdf:
					break;
				case word:
					break;
			}
			fileName = FileUtils.encodeFilename(fileName, request);
			String headStr = "attachment; filename=\"" + fileName + "\"";
			responseServlet.setHeader("content-Type", "application/vnd.ms-excel");
			responseServlet.setHeader("Content-Disposition", headStr);
			out = responseServlet.getOutputStream();
			ExportExcel ex = new ExportExcel("非督导绩效查询", tableTitles,tableFields, arr);
			ex.export(out);
			out.flush();
			out.close();
			response.put(ResponseJson.status, 1002);
			response.put(ResponseJson.msg, "删除成功");
		} catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
		} catch (Exception e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Export_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject toshenhe(JSONObject json, JSONObject response) {
		JSONArray json1=json.getJSONArray("shenheData");
		int c = json.getJSONArray("shenheData").size();    
		String whereField = "NO";
    	String updateTable = "hrm_jd_performance";
    	String updateField = "flag,shr,shsj=now()";
    	try { 
    		DBTools2.updateBatch(updateField, whereField, updateTable, json1);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_finish_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;  
	}
	@Override
	public JSONObject batchdel(JSONObject json, JSONObject response) {
		String whereField = "NO";
    	String delTable = "hrm_jd_performance";
    	try {
			DBTools2.delete(whereField, delTable,json);
			DBTools2.delete(whereField, "hrm_jd_performance_detail",json);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject tozuofei(JSONObject json, JSONObject response) {
		JSONArray json1=json.getJSONArray("zuofeiData");
		int c = json.getJSONArray("zuofeiData").size();    
		String whereField = "NO";
    	String updateTable = "hrm_jd_performance";
    	String updateField = "flag,zfr,zfsj=now()";
    	try { 
    		DBTools2.updateBatch(updateField, whereField, updateTable, json1);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_finish_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;  
	}
}
